﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace SmartBlast
{
    class DataBase_BUS
    {
        private static DataBase_BUS _instance = null;
        private DataBase_DAO databaseDAO;

        public static DataBase_BUS getInstance()
        {
            if (_instance == null)
            {
                _instance = new DataBase_BUS();
            }
            return _instance;
        }

        private DataBase_BUS()
        {
            databaseDAO = DataBase_DAO.getInstance();
        }

        public DataTable selectRecrordFromTable(string tblName, string[] fieldNames, string[] fields)
        {
            if (fields == null)
            {
                return selectAllRecordsFromTable(tblName);
            }

            string restriction = concatenateFieldsAndFieldNamesToRestriction(fieldNames, fields);
            string query = "select * from " + tblName + " where " + restriction;
            // execute query

         return databaseDAO.executeSelectQuery(query);
        }

        public DataTable selectRecordFromJoinedTables(string tblName1, string tblName2, string tblName3, string keyFieldName1, string keyFieldName2,string[] fieldNamesFromThirdTable , string[] fieldsFromThirdTable)
        {

            string joinRestriction = generateJoinRestriction(tblName1, tblName2, tblName3,
                keyFieldName1, keyFieldName2, fieldNamesFromThirdTable[0], fieldNamesFromThirdTable[1]);
            string restrictionOnJoinedTable = concatenateFieldsAndFieldNamesToRestriction(fieldNamesFromThirdTable, fieldsFromThirdTable);
            string restriction = joinRestriction + " AND " + restrictionOnJoinedTable;
            string selection = "select " + tblName1 + ".* , " + tblName2 + ".* , " + tblName3 + ".* ";
            string from = tblName1 + "," + tblName2 + "," + tblName3;
            string query = selection + " from " + from + " where " + restriction;
            return databaseDAO.executeSelectQuery(query);
            

        }

        public DataTable selectAllRecordsFromJoinedTables(string tblName1, string tblName2, string tblName3, string key1, string key2, string key3_1, string key3_2)
        {
        
            string joinRestriction = generateJoinRestriction(tblName1, tblName2, tblName3,
                       key1, key2, key3_1, key3_2);
            string selection = "select " + tblName1 + ".* , " + tblName2 + ".* , " + tblName3 + ".* ";
            string from = tblName1 + "," + tblName2 + "," + tblName3;
            string query = selection + " from " + from + " where " + joinRestriction;
            return databaseDAO.executeSelectQuery(query);
        }


        private string generateJoinRestriction(string tblName1,string tblName2,string tblName3,string keyName1,string keyName2,string keyName3_1, string keyName3_2)
        {
 	        string ans =tblName1+"."+keyName1+"="+tblName3+"."+keyName3_1 + " AND ";
            ans+=tblName2+"."+keyName2+"="+tblName3+"."+keyName3_2;
            return ans;
        }

        private DataTable selectAllRecordsFromTable(string tblName)
        {
            string query = "select * from " + tblName;
            return databaseDAO.executeSelectQuery(query);
        }

        public void insertRecordIntoTable(string tblName, string[] fields)
        {
            string values = concatenateFieldsToRecordInsert(fields);
            string query = "insert into " + tblName + " values " + values;
            databaseDAO.executeNonSelectQuery(query);
        }

        

        public void updateRecordInTable(string tblName, string[] fieldNames, string[] fields, string keyField, string keyFieldName)
        {
            string restriction = keyFieldName+"="+keyField;
            string setValues = concatenateFieldsToValuesSet(fieldNames, fields);
            string query = "update " + tblName + " set " + setValues + " where " + restriction;
            databaseDAO.executeNonSelectQuery(query);
        }

        public void deleteRecordFromTable(string tblName, string keyField, string keyFieldName)
        {

            string restriction = keyFieldName + "=" + keyField;
            string query = "delete from " + tblName + " where " + restriction;
            databaseDAO.executeNonSelectQuery(query);
        }

        private string concatenateFieldsToRecordInsert(string[] fields)
        {
            string ans = "(";
            for (int i = 0; i < fields.Length; i++)
            {
                if (i != 0)
                {
                    ans += ",";
                }
                ans += fields[i];
            }
            ans += ")";
            return ans;
        }

        private string concatenateFieldsAndFieldNamesToRestriction(string[] fieldNames, string[] fields)
        {
            return concatenateFieldsAndFieldNames(fieldNames, fields, "AND");
        }

        private string concatenateFieldsToValuesSet(string[] fieldNames, string[] fields)
        {
            return concatenateFieldsAndFieldNames(fieldNames, fields, ",");
        }

        private string concatenateFieldsAndFieldNames(string[] fieldNames,string[] fields , string delimiter)
        {
            string ans = "";
            for (int i = 0; i < fields.Length; i++)
            {
                if (fields[i] == "" || fields[i] == "''") {continue;}
                if (i != 0 && ans!="")
                {
                    ans += " " + delimiter + " ";
                }
                ans += fieldNames[i] + "=" + fields[i];

            }
            return ans;
        }


        public void makeConnection(string serverName)
        {
            databaseDAO.makeConnection(serverName);
        }




    }
}
